//DevNETCoder
//CopyRight By DevNET Group
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using STT.Entities;
using STT.DataAccess;
using System.Data.SqlTypes;
using System.IO;

namespace STT.Business
{
    public class BTbHocVien
    {                       
        public static DataTable Count_SVLop(int ML)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"maLop", ML);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "Dem_SinhVien_Lop", pr);
            return all;
        }
        
        public static void UpdateChucVuHVTrongLop(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            pr[1] = new SqlParameter(@"ChucVuHV", HocVien.ChucVuTrongLop);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_UpdateChucVuHVTrongLop", pr);
        }
        public static void UpdateTNHV(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbChat_UpdateTNHV", pr);
        }
        public static DataTable Get_SiSo(int ML)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"maLop", ML);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "Lay_Si_So_Lop", pr);
            return all;
        }
        public static DataTable HocVi()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectHocVi", null);
            return all;
        }
        public static DataTable HocHam()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectHocHam", null);
            return all;
        }
        public static string TaoMaHV()
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"ReturnValue", SqlDbType.NChar);
            pr[0].Size = 3;
            pr[0].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_TaoMaHV", pr);
            return pr[0].Value.ToString();
        }
        public static void Update_Lop(String MaHV, int ML)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            pr[1] = new SqlParameter(@"MaLop", ML);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update_Lop", pr);
        }
        public static DataTable SelectAll_Xuat(int malop,string nguoitao)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaLop", malop);
            pr[1] = new SqlParameter(@"NguoiTao", nguoitao);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAll_Xuat", pr);
            return all;
        }
        public static void Update_GhiChu(String MaHV, String GC)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            pr[1] = new SqlParameter(@"GhiChu", GC);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update_GC", pr);
        }

        public static void Update_GhiChu(String GC)
        {
            SqlParameter[] pr = new SqlParameter[1];

            pr[0] = new SqlParameter(@"GhiChu", GC);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update_GhiChu", pr);
        }
        public static bool KiemTraMaHV(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_KiemTraMaHV", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

    
        public static void Update_Lop(String MaHV, String GC)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            pr[1] = new SqlParameter(@"GhiChu", GC);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update_Lop", pr);
        }
        public static void Update_Ten(int MaHV, String ML)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaCa", MaHV);
            pr[1] = new SqlParameter(@"TenCa", ML);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbCaHoc_Update_Lop", pr);
        }
        public static void Update_Lop_View(String MaHV, int MaLop)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            pr[1] = new SqlParameter(@"MaLop", MaLop);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update_Lop_View", pr);
        }
        public static void Update_Lop_Xoa(String MaHV, int MaLop)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", MaHV);

            pr[1] = new SqlParameter(@"MaLop", MaLop);

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update_Lop_Xoa", pr);
        }
        //-----------------------------------------------------------//
        public static DataTable Select_PhanLop(String GC)
        {
            SqlParameter[] pr = new SqlParameter[1];

            pr[0] = new SqlParameter(@"GhiChu", GC);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_Select_PhanLop", pr);
            return all;
        }
     

//-----------------------------------------------------------//
        public static DataTable SelectAll()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAll", null);
            return all;
        }
        public static DataTable HocVien_LayDonVi(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAllCustom", null);
            return all;
        }
        public static DataTable SelectAllCustom()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAllCustom", null);
            return all;
        }
        public static DataTable SelectAllCustomTest(ETbGiangVien GiangVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV", GiangVien.MaGV);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAllCustomTest", pr);
            return all;
        }

        public static DataTable SelectTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectTop", pr);
            return all;
        }
        public static DataTable Export()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_Export", null);
            return all;
        }

        public static DataTable SearchPhanLop(String id, String name, String idRegency, String idUnit, int tuoi, int sokhoahoc, string trinhdohv, int maNN)
        {

            SqlParameter[] pr = new SqlParameter[8];
            pr[0] = new SqlParameter(@"MaHV", id == "" ? (object)DBNull.Value : id);
            pr[1] = new SqlParameter(@"TenHV", name == "" ? (object)DBNull.Value : name);
            pr[2] = new SqlParameter(@"ChucVu", idRegency == "" ? (object)DBNull.Value : idRegency);
            pr[3] = new SqlParameter(@"DonVi", idUnit == "" ? (object)DBNull.Value : idUnit);
            pr[4] = new SqlParameter(@"Tuoi", tuoi == -1 ? (object)DBNull.Value : tuoi);
            pr[5] = new SqlParameter(@"sokhoahoc", sokhoahoc == -1 ? (object)DBNull.Value : sokhoahoc);
            pr[6] = new SqlParameter(@"TrinhDoHV", trinhdohv == "" ? (object)DBNull.Value : trinhdohv);
            pr[7] = new SqlParameter(@"MaNgoaiNgu", maNN == -1 ? (object)DBNull.Value : maNN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TimKiemPhanLop", pr);
            return all;
        }
        //public static bool InsertHV(string strsql)
        //{
        //    List<SqlParameter> listparam = new List<SqlParameter>();
        //     listparam.Add(new SqlParameter("@SQL", strSql));
        //     SqlHelper.ExecuteNonQuery( CommandType.StoredProcedure, "Cuong_InsertTable", listparam);
        //}
        public static bool InsertJobKeyExec(string strSql)
        {
            try
            {
                List<SqlParameter> listparam = new List<SqlParameter>();
                listparam.Add(new SqlParameter("@SQL", strSql));
                return SqlHelper.ExecuteQuery(CommandType.StoredProcedure, "Cuong_InsertTable", listparam);
            }
            catch (Exception)
            {

                return false;
            }
        }
//-----------------------------------------------------------//
       
        public static void Insert(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[48];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Ten", OTbHocVien.Ten);
            pr[3] = new SqlParameter(@"GioiTinh", OTbHocVien.GioiTinh);
            pr[4] = new SqlParameter(@"ChucVu", OTbHocVien.ChucVu.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.ChucVu);
            pr[5] = new SqlParameter(@"PhongBan", OTbHocVien.PhongBan.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.PhongBan);
            pr[6] = new SqlParameter(@"NgaySinh", OTbHocVien.NgaySinh == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgaySinh);
            pr[7] = new SqlParameter(@"DanToc", OTbHocVien.DanToc.ToString() == "" ? (object)DBNull.Value : OTbHocVien.DanToc);
            pr[8] = new SqlParameter(@"TinhTrangHD", OTbHocVien.TinhTrangHD.ToString() == "" ? (object)DBNull.Value : OTbHocVien.TinhTrangHD);
            pr[9] = new SqlParameter(@"ToNhom", OTbHocVien.ToNhom.ToString() == "" ? (object)DBNull.Value : OTbHocVien.ToNhom);
            pr[10] = new SqlParameter(@"MaNgachLuong", OTbHocVien.MaNgachLuong.ToString() == "" ? (object)DBNull.Value : OTbHocVien.MaNgachLuong);
            pr[11] = new SqlParameter(@"BacLuong", OTbHocVien.BacLuong.ToString() == "" ? (object)DBNull.Value : OTbHocVien.BacLuong);
            pr[12] = new SqlParameter(@"HeSoLuong", OTbHocVien.HeSoLuong.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.HeSoLuong);
            pr[13] = new SqlParameter(@"NgayHieuLucLuong", OTbHocVien.NgayHieuLucLuong == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayHieuLucLuong);
            pr[14] = new SqlParameter(@"LuongKhoan", OTbHocVien.LuongKhoan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.LuongKhoan);
            pr[15] = new SqlParameter(@"HeSoBaoLuu", OTbHocVien.HeSoBaoLuu.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.HeSoBaoLuu);
            pr[16] = new SqlParameter(@"SoBHXH", OTbHocVien.SoBHXH.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoBHXH);
            pr[17] = new SqlParameter(@"SoBHYT", OTbHocVien.SoBHYT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoBHYT);
            pr[18] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoCMT);
            pr[19] = new SqlParameter(@"NgayCapCMT", OTbHocVien.NgayCapCMT == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayCapCMT);
            pr[20] = new SqlParameter(@"NoiCapCMT", OTbHocVien.NoiCapCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.NoiCapCMT);
            pr[21] = new SqlParameter(@"TDVanHoa", OTbHocVien.TDVanHoa.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TDVanHoa);
            pr[22] = new SqlParameter(@"HocHam", OTbHocVien.HocHam.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HocHam);
            pr[23] = new SqlParameter(@"HocVi", OTbHocVien.HocVi.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HocVi);
            pr[24] = new SqlParameter(@"NganhNgheDaoTao", OTbHocVien.NganhNgheDaoTao.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.NganhNgheDaoTao);
            pr[25] = new SqlParameter(@"HinhThucDaoTao", OTbHocVien.HinhThucDaoTao.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HinhThucDaoTao);
            pr[26] = new SqlParameter(@"TrinhDoQLKT", OTbHocVien.TrinhDoQLKT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.TrinhDoQLKT);
            pr[27] = new SqlParameter(@"TrinhDoLLCT", OTbHocVien.TrinhDoLLCT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.TrinhDoLLCT);
            pr[28] = new SqlParameter(@"NgayVaoNganh", OTbHocVien.NgayVaoNganh == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayVaoNganh);
            pr[29] = new SqlParameter(@"NgayVaoCoQuan", OTbHocVien.NgayVaoCoQuan == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayVaoCoQuan);
            pr[30] = new SqlParameter(@"NgayTuyenDung", OTbHocVien.NgayTuyenDung == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayTuyenDung);
            pr[31] = new SqlParameter(@"TrinhDo", OTbHocVien.TrinhDo.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TrinhDo);
            pr[32] = new SqlParameter(@"CongViecDangLam", OTbHocVien.CongViecDangLam.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.CongViecDangLam);
            pr[33] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.QueQuan);
            pr[34] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.DiaChi);
            pr[35] = new SqlParameter(@"Anh", OTbHocVien.Anh.ToString().Trim() == "" ? "" : OTbHocVien.Anh);
            pr[36] = new SqlParameter(@"HieuTrinhThanNhan", OTbHocVien.HieuTrinhThanNhan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HieuTrinhThanNhan);
            pr[37] = new SqlParameter(@"DangVien", OTbHocVien.DangVien.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.DangVien);
            pr[38] = new SqlParameter(@"NgayVaoDang", OTbHocVien.NgayVaoDang == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayVaoDang);
            pr[39] = new SqlParameter(@"Sdt", OTbHocVien.Sdt.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.Sdt);
            pr[40] = new SqlParameter(@"Email", OTbHocVien.Email.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.Email);
            pr[41] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
            pr[42] = new SqlParameter(@"ChucVuTrongLop", OTbHocVien.ChucVuTrongLop.ToString().Trim() == "" ? "" : OTbHocVien.ChucVuTrongLop);
            pr[43] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
            pr[44] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            pr[45] = new SqlParameter(@"TrangThaiTB", OTbHocVien.TrangThaiTB);
            pr[46] = new SqlParameter(@"Loai", OTbHocVien.Loai);
            pr[47] = new SqlParameter(@"NguoiTao", OTbHocVien.NguoiTao);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Insert", pr);
        }

        //-----------------------------------------------------------//
        public static void UpdateByExcel(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[48];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Ten", OTbHocVien.Ten);
            pr[3] = new SqlParameter(@"GioiTinh", OTbHocVien.GioiTinh);
            pr[4] = new SqlParameter(@"ChucVu", OTbHocVien.ChucVu.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.ChucVu);
            pr[5] = new SqlParameter(@"PhongBan", OTbHocVien.PhongBan.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.PhongBan);
            pr[6] = new SqlParameter(@"NgaySinh", OTbHocVien.NgaySinh == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgaySinh);
            pr[7] = new SqlParameter(@"DanToc", OTbHocVien.DanToc.ToString() == "" ? (object)DBNull.Value : OTbHocVien.DanToc);
            pr[8] = new SqlParameter(@"TinhTrangHD", OTbHocVien.TinhTrangHD.ToString() == "" ? (object)DBNull.Value : OTbHocVien.TinhTrangHD);
            pr[9] = new SqlParameter(@"ToNhom", OTbHocVien.ToNhom.ToString() == "" ? (object)DBNull.Value : OTbHocVien.ToNhom);
            pr[10] = new SqlParameter(@"MaNgachLuong", OTbHocVien.MaNgachLuong.ToString() == "" ? (object)DBNull.Value : OTbHocVien.MaNgachLuong);
            pr[11] = new SqlParameter(@"BacLuong", OTbHocVien.BacLuong.ToString() == "" ? (object)DBNull.Value : OTbHocVien.BacLuong);
            pr[12] = new SqlParameter(@"HeSoLuong", OTbHocVien.HeSoLuong.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.HeSoLuong);
            pr[13] = new SqlParameter(@"NgayHieuLucLuong", OTbHocVien.NgayHieuLucLuong == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayHieuLucLuong);
            pr[14] = new SqlParameter(@"LuongKhoan", OTbHocVien.LuongKhoan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.LuongKhoan);
            pr[15] = new SqlParameter(@"HeSoBaoLuu", OTbHocVien.HeSoBaoLuu.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.HeSoBaoLuu);
            pr[16] = new SqlParameter(@"SoBHXH", OTbHocVien.SoBHXH.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoBHXH);
            pr[17] = new SqlParameter(@"SoBHYT", OTbHocVien.SoBHYT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoBHYT);
            pr[18] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoCMT);
            pr[19] = new SqlParameter(@"NgayCapCMT", OTbHocVien.NgayCapCMT == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayCapCMT);
            pr[20] = new SqlParameter(@"NoiCapCMT", OTbHocVien.NoiCapCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.NoiCapCMT);
            pr[21] = new SqlParameter(@"TDVanHoa", OTbHocVien.TDVanHoa.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TDVanHoa);
            pr[22] = new SqlParameter(@"HocHam", OTbHocVien.HocHam.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HocHam);
            pr[23] = new SqlParameter(@"HocVi", OTbHocVien.HocVi.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HocVi);
            pr[24] = new SqlParameter(@"NganhNgheDaoTao", OTbHocVien.NganhNgheDaoTao.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.NganhNgheDaoTao);
            pr[25] = new SqlParameter(@"HinhThucDaoTao", OTbHocVien.HinhThucDaoTao.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HinhThucDaoTao);
            pr[26] = new SqlParameter(@"TrinhDoQLKT", OTbHocVien.TrinhDoQLKT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.TrinhDoQLKT);
            pr[27] = new SqlParameter(@"TrinhDoLLCT", OTbHocVien.TrinhDoLLCT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.TrinhDoLLCT);
            pr[28] = new SqlParameter(@"NgayVaoNganh", OTbHocVien.NgayVaoNganh == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayVaoNganh);
            pr[29] = new SqlParameter(@"NgayVaoCoQuan", OTbHocVien.NgayVaoCoQuan == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayVaoCoQuan);
            pr[30] = new SqlParameter(@"NgayTuyenDung", OTbHocVien.NgayTuyenDung == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayTuyenDung);
            pr[31] = new SqlParameter(@"TrinhDo", OTbHocVien.TrinhDo.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TrinhDo);
            pr[32] = new SqlParameter(@"CongViecDangLam", OTbHocVien.CongViecDangLam.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.CongViecDangLam);
            pr[33] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.QueQuan);
            pr[34] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.DiaChi);
            pr[35] = new SqlParameter(@"Anh", OTbHocVien.Anh.ToString().Trim() == "" ? "" : OTbHocVien.Anh);
            pr[36] = new SqlParameter(@"HieuTrinhThanNhan", OTbHocVien.HieuTrinhThanNhan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HieuTrinhThanNhan);
            pr[37] = new SqlParameter(@"DangVien", OTbHocVien.DangVien.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.DangVien);
            pr[38] = new SqlParameter(@"NgayVaoDang", OTbHocVien.NgayVaoDang == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayVaoDang);
            pr[39] = new SqlParameter(@"Sdt", OTbHocVien.Sdt.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.Sdt);
            pr[40] = new SqlParameter(@"Email", OTbHocVien.Email.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.Email);
            pr[41] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
            pr[42] = new SqlParameter(@"ChucVuTrongLop", OTbHocVien.ChucVuTrongLop.ToString().Trim() == "" ? "" : OTbHocVien.ChucVuTrongLop);
            pr[43] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
            pr[44] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            pr[45] = new SqlParameter(@"TrangThaiTB", OTbHocVien.TrangThaiTB);
            pr[46] = new SqlParameter(@"Loai", OTbHocVien.Loai);
            pr[47] = new SqlParameter(@"NguoiTao", OTbHocVien.NguoiTao);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_UpdateByExcel", pr);
        }
        public static void Insert1(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[26];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Ten", OTbHocVien.Ten);
            pr[3] = new SqlParameter(@"GioiTinh", OTbHocVien.GioiTinh);
            pr[4] = new SqlParameter(@"ChucVu", OTbHocVien.ChucVu.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.ChucVu);
            pr[5] = new SqlParameter(@"PhongBan", OTbHocVien.PhongBan.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.PhongBan);
            pr[6] = new SqlParameter(@"NgaySinh", OTbHocVien.NgaySinh == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgaySinh);
            pr[7] = new SqlParameter(@"DanToc", OTbHocVien.DanToc.ToString() == "" ? (object)DBNull.Value : OTbHocVien.DanToc);
            pr[8] = new SqlParameter(@"MaNgachLuong", OTbHocVien.MaNgachLuong.ToString() == "" ? (object)DBNull.Value : OTbHocVien.MaNgachLuong);
            pr[9] = new SqlParameter(@"HeSoLuong", OTbHocVien.HeSoLuong.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.HeSoLuong);
            pr[10] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoCMT);
            pr[11] = new SqlParameter(@"NgayCapCMT", OTbHocVien.NgayCapCMT == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayCapCMT);
            pr[12] = new SqlParameter(@"NoiCapCMT", OTbHocVien.NoiCapCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.NoiCapCMT);
            pr[13] = new SqlParameter(@"TDVanHoa", OTbHocVien.TDVanHoa.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TDVanHoa);
            pr[14] = new SqlParameter(@"TrinhDo", OTbHocVien.TrinhDo.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TrinhDo);
            pr[15] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.QueQuan);
            pr[16] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.DiaChi);
            pr[17] = new SqlParameter(@"Anh", OTbHocVien.Anh.ToString().Trim() == "" ? "" : OTbHocVien.Anh);
            pr[18] = new SqlParameter(@"HieuTrinhThanNhan", OTbHocVien.HieuTrinhThanNhan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HieuTrinhThanNhan);
            pr[19] = new SqlParameter(@"Sdt", OTbHocVien.Sdt.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.Sdt);
            pr[20] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
            pr[21] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
            pr[22] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            pr[23] = new SqlParameter(@"TrangThaiTB", OTbHocVien.TrangThaiTB);
            pr[24] = new SqlParameter(@"Loai", OTbHocVien.Loai);
            pr[25] = new SqlParameter(@"NguoiTao", OTbHocVien.NguoiTao);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_InsertMotHV", pr);
        }
        public static DataTable StatisticsStudentByCourses(ETbHocVien HocVien, string IdKhoaHoc,string TenDN,string Tuoi)
        {
            SqlParameter[] pr = new SqlParameter[8];
            pr[0] = new SqlParameter(@"IdCourses", IdKhoaHoc == "-1" ? (object)DBNull.Value : IdKhoaHoc.ToString());
            pr[1] = new SqlParameter(@"IdUnit", HocVien.MaDonVi == -1 ? (object)DBNull.Value : HocVien.MaDonVi.ToString());
            pr[2] = new SqlParameter(@"IdClass", HocVien.MaLop == -1 ? (object)DBNull.Value : HocVien.MaLop.ToString());
            pr[3] = new SqlParameter(@"IdRegency", HocVien.ChucVu == -1 ? (object)DBNull.Value : HocVien.ChucVu);
            pr[4] = new SqlParameter(@"TenDN", TenDN);
            pr[5] = new SqlParameter(@"HocHam", HocVien.HocHam == "-1" ? (object)DBNull.Value : HocVien.HocHam.ToString());
            pr[6] = new SqlParameter(@"HocVi", HocVien.HocVi == "-1" ? (object)DBNull.Value : HocVien.HocVi.ToString());
            pr[7] = new SqlParameter(@"Tuoi", Tuoi == "" ? (object)DBNull.Value : Tuoi);

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "StatisticsStudentByCourses", pr);
            return all;
        }
//-----------------------------------------------------------//
        public static void Update(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[26];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Ten", OTbHocVien.Ten);
            pr[3] = new SqlParameter(@"GioiTinh", OTbHocVien.GioiTinh);
            pr[4] = new SqlParameter(@"ChucVu", OTbHocVien.ChucVu.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.ChucVu);
            pr[5] = new SqlParameter(@"PhongBan", OTbHocVien.PhongBan.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.PhongBan);
            pr[6] = new SqlParameter(@"NgaySinh", OTbHocVien.NgaySinh == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgaySinh);
            pr[7] = new SqlParameter(@"DanToc", OTbHocVien.DanToc.ToString() == "" ? (object)DBNull.Value : OTbHocVien.DanToc);
            pr[8] = new SqlParameter(@"MaNgachLuong", OTbHocVien.MaNgachLuong.ToString() == "" ? (object)DBNull.Value : OTbHocVien.MaNgachLuong);
            pr[9] = new SqlParameter(@"HeSoLuong", OTbHocVien.HeSoLuong.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.HeSoLuong);
            pr[10] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.SoCMT);
            pr[11] = new SqlParameter(@"NgayCapCMT", OTbHocVien.NgayCapCMT == DateTime.MinValue ? (object)DBNull.Value : OTbHocVien.NgayCapCMT);
            pr[12] = new SqlParameter(@"NoiCapCMT", OTbHocVien.NoiCapCMT.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.NoiCapCMT);
            pr[13] = new SqlParameter(@"TDVanHoa", OTbHocVien.TDVanHoa.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TDVanHoa);
            pr[14] = new SqlParameter(@"TrinhDo", OTbHocVien.TrinhDo.ToString().Trim() == "-1" ? (object)DBNull.Value : OTbHocVien.TrinhDo);
            pr[15] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.QueQuan);
            pr[16] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.DiaChi);
            pr[17] = new SqlParameter(@"Anh", OTbHocVien.Anh.ToString().Trim() == "" ? "" : OTbHocVien.Anh);
            pr[18] = new SqlParameter(@"HieuTrinhThanNhan", OTbHocVien.HieuTrinhThanNhan.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.HieuTrinhThanNhan);
            pr[19] = new SqlParameter(@"Sdt", OTbHocVien.Sdt.ToString().Trim() == "" ? (object)DBNull.Value : OTbHocVien.Sdt);
            pr[20] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
            pr[21] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
            pr[22] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            pr[23] = new SqlParameter(@"TrangThaiTB", OTbHocVien.TrangThaiTB);
            pr[24] = new SqlParameter(@"Loai", OTbHocVien.Loai);
            pr[25] = new SqlParameter(@"NguoiTao", OTbHocVien.NguoiTao);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Update", pr);
        }
        public static void UpdateTBHV(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TrangThaiTB", OTbHocVien.TrangThaiTB);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_UpdateSoTB", pr);
        }
        public static void DeleteByMaHV(string MaHV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_NgoaiNgu_DeleteByMaHV", pr);
        }
        public static void UpdateTB()
        {
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_UpdateTB", null);
        }
        public static void Delete(string MaHV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Delete", pr);
        }
        //-----------------------------------------------------------//
        public static DataSet SelectByID(string MaHV)
        {
            ETbHocVien HocVien = new ETbHocVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectByID", pr);
            all.TableName = "Row";
            DataSet ds = new DataSet();
            ds.Tables.Add(all);
            return ds;
        }
        //-----------------------------------------------------------//
        public static DataTable SelectByIDMaHV(string MaHV)
        {
        
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectByMaHV", pr);
         
            return all;
        }
        public static DataTable SelectHocVienByLop(string MaGV, string MaLop)
        {

            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaGV", MaGV);
            pr[1] = new SqlParameter(@"MaLop", MaLop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectByLop", pr);

            return all;
        }

        //TbHocVien_SelectByMaHV


        public static DataTable SelectStudentsBySearch(ETbHocVien HocVien, int Tuoi, int Number, int IdLanguage, int IdClass,string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[13];
            pr[0] = new SqlParameter(@"Id", HocVien.MaHV == "" ? (object)DBNull.Value : HocVien.MaHV.Trim());
            pr[1] = new SqlParameter(@"Name", HocVien.TenHV == "" ? (object)DBNull.Value : HocVien.TenHV);
            pr[2] = new SqlParameter(@"IdRegency", HocVien.ChucVu == -1 ? (object)DBNull.Value : HocVien.ChucVu);
            pr[3] = new SqlParameter(@"IdUnit", HocVien.MaDonVi == -1 ? (object)DBNull.Value : HocVien.MaDonVi);
            pr[4] = new SqlParameter(@"IdQualification", HocVien.TrinhDo == -1 ? (object)DBNull.Value : HocVien.TrinhDo);
            pr[5] = new SqlParameter(@"Literacy", HocVien.TDVanHoa == -1 ? (object)DBNull.Value : HocVien.TDVanHoa);
            pr[6] = new SqlParameter(@"Number", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[7] = new SqlParameter(@"QueQuan", HocVien.QueQuan == "" ? (object)DBNull.Value : HocVien.QueQuan);
            pr[8] = new SqlParameter(@"IdLanguage", IdLanguage == -1 ? (object)DBNull.Value : IdLanguage.ToString());
            pr[9] = new SqlParameter(@"IdClass", IdClass == -1 ? (object)DBNull.Value : IdClass.ToString());
            pr[10] = new SqlParameter(@"DiaChi", HocVien.DiaChi==""? (object)DBNull.Value : HocVien.DiaChi);
            pr[11] = new SqlParameter(@"Tuoi", Tuoi == -1 ? (object)DBNull.Value : Tuoi);
            pr[12] = new SqlParameter(@"TenDN", TenDN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectStudentBySearch", pr);
            return all;
        }

//-----------------------------------------------------------//
        //public static void Delete(string MaHV)
        //{
        //    SqlParameter[] pr = new SqlParameter[1];
        //    pr[0] = new SqlParameter(@"MaHV", MaHV);
        //    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Delete", pr);
        //}
//-----------------------------------------------------------//
        public static bool KiemTraDuLieu(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            pr[1] = new SqlParameter(@"SoCMT", HocVien.SoCMT);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_KiemTraDuLieu", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public static bool KiemTraGiamDoc(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaDonVi", HocVien.MaDonVi);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_KiemTraGiamDoc", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public static DataTable LayGiamDoc(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaDonVi", HocVien.MaDonVi);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_KiemTraGiamDoc", pr);
            return all;
        }
        
        public static bool KiemTraImport(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            pr[1] = new SqlParameter(@"SoCMT", HocVien.SoCMT);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_KiemTraImport", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
//-----------------------------------------------------------//
        public static DataTable SearchHocVien(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"TenHV", HocVien.TenHV);
            pr[1] = new SqlParameter(@"MaNguoiTao", HocVien.NguoiTao);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_Search", pr);
            return all;
        }

//-----------------------------------------------------------//
        public static ETbHocVien SelectByMaHV(string MaHV)
        {
            ETbHocVien OTbHocVien = new ETbHocVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbHocVien_SelectByMaHV", pr);
            if (idr.Read())
               OTbHocVien = GetOneTbHocVien(idr);
            idr.Close();
            idr.Dispose();
            return OTbHocVien;
        }
        ////-----------------------------------------------------------//
        public static DataTable SelectByLop(ETbHocVien HocVien)
        {
            ETbHocVien OTbHocVien = new ETbHocVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaLop", HocVien.MaLop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_GetByMaLop", pr);
            return all;
        }
        ////-----------------------------------------------------------//
        public static DataTable SelectByLopExport(ETbHocVien HocVien)
        {
            ETbHocVien OTbHocVien = new ETbHocVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaLop", HocVien.MaLop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_GetByMaLopExport", pr);
            return all;
        }
        public static bool TestByID(string MaHV)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV", MaHV);
            pr[1] = new SqlParameter(@"TestID", SqlDbType.Bit);
            pr[1].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_TestByID", pr);
            return Convert.ToBoolean(pr[1].Value);
        }
//-----------------------------------------------------------//
        //public static DataTable SelectStudentsBySearch(ETbHocVien HocVien, int IdCourses, int Number, int IdLanguage, int IdClass)
        //{
        //    SqlParameter[] pr = new SqlParameter[10];
        //    pr[0] = new SqlParameter(@"Id", HocVien.MaHV == "-1" ? (object)DBNull.Value : HocVien.MaHV.ToString());
        //    pr[1] = new SqlParameter(@"Name", HocVien.TenHV == "" ? (object)DBNull.Value : HocVien.TenHV);
        //    pr[2] = new SqlParameter(@"IdRegency", HocVien.MaChucVu == -1 ? (object)DBNull.Value : HocVien.MaChucVu.ToString());
        //    pr[3] = new SqlParameter(@"IdUnit", HocVien.MaDonVi == -1 ? (object)DBNull.Value : HocVien.MaDonVi.ToString());
        //    pr[4] = new SqlParameter(@"IdQualification", HocVien.MaTDChuyenMon == -1 ? (object)DBNull.Value : HocVien.MaTDChuyenMon.ToString());
        //    pr[5] = new SqlParameter(@"Literacy", HocVien.MaTDHocVan == -1 ? (object)DBNull.Value : HocVien.MaTDHocVan.ToString());
        //    pr[6] = new SqlParameter(@"Number", Number == -1 ? (object)DBNull.Value : Number.ToString());
        //    pr[7] = new SqlParameter(@"IdCourses", IdCourses == -1 ? (object)DBNull.Value : IdCourses.ToString());
        //    pr[8] = new SqlParameter(@"IdLanguage", IdLanguage == -1 ? (object)DBNull.Value : IdLanguage.ToString());
        //    pr[9] = new SqlParameter(@"IdClass", IdClass == -1 ? (object)DBNull.Value : IdClass.ToString());

        //    DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectStudentBySearch", pr);
        //    return all;
        //}

//-----------------------------------------------------------//
        private static ETbHocVien GetOneTbHocVien(IDataReader idr)
        {
            ETbHocVien OTbHocVien = new ETbHocVien();
            if (idr["MaHV"] != DBNull.Value)
                OTbHocVien.MaHV = (string)idr["MaHV"];
            if (idr["TenHV"] != DBNull.Value)
                OTbHocVien.TenHV = (string)idr["TenHV"];
            if (idr["Ten"] != DBNull.Value)
                OTbHocVien.Ten = (string)idr["Ten"];
            //if (idr["NamSinh"] != DBNull.Value)
            //    OTbHocVien.NamSinh = (int)idr["NamSinh"];
            if (idr["GioiTinh"] != DBNull.Value)
                OTbHocVien.GioiTinh = (string)idr["GioiTinh"];
            if (idr["ChucVu"] != DBNull.Value)
                OTbHocVien.ChucVu = (int)idr["ChucVu"];
            if (idr["PhongBan"] != DBNull.Value)
                OTbHocVien.PhongBan = (int)idr["PhongBan"];
            if (idr["NgaySinh"] != DBNull.Value)
                OTbHocVien.NgaySinh = (DateTime)idr["NgaySinh"];
            if (idr["DanToc"] != DBNull.Value)
                OTbHocVien.DanToc = (string)idr["DanToc"];
            if (idr["ToNhom"] != DBNull.Value)
                OTbHocVien.ToNhom = (string)idr["ToNhom"];
            if (idr["QueQuan"] != DBNull.Value)
                OTbHocVien.QueQuan = (string)idr["QueQuan"];
            if (idr["MaNgachLuong"] != DBNull.Value)
                OTbHocVien.MaNgachLuong = (string)idr["MaNgachLuong"];
            if (idr["BacLuong"] != DBNull.Value)
                OTbHocVien.BacLuong = (string)idr["BacLuong"];
            if (idr["HeSoLuong"] != DBNull.Value)
                OTbHocVien.HeSoLuong = (double)idr["HeSoLuong"];
            if (idr["NgayHieuLucLuong"] != DBNull.Value)
                OTbHocVien.NgayHieuLucLuong = (DateTime)idr["NgayHieuLucLuong"];
            if (idr["LuongKhoan"] != DBNull.Value)
                OTbHocVien.LuongKhoan = (string)idr["LuongKhoan"];
            if (idr["HeSoBaoLuu"] != DBNull.Value)
                OTbHocVien.HeSoBaoLuu = (double)idr["HeSoBaoLuu"];
            if (idr["SoBHXH"] != DBNull.Value)
                OTbHocVien.SoBHXH = (string)idr["SoBHXH"];
            if (idr["SoBHYT"] != DBNull.Value)
                OTbHocVien.SoBHYT = (string)idr["SoBHYT"];
            if (idr["SoCMT"] != DBNull.Value)
                OTbHocVien.SoCMT = (string)idr["SoCMT"];
            if (idr["NgayCapCMT"] != DBNull.Value)
                OTbHocVien.NgayCapCMT = (DateTime)idr["NgayCapCMT"];
            if (idr["Sdt"] != DBNull.Value)
                OTbHocVien.Sdt = (string)idr["Sdt"];
            if (idr["NoiCapCMT"] != DBNull.Value)
                OTbHocVien.NoiCapCMT = (string)idr["NoiCapCMT"];
            if (idr["Anh"] != DBNull.Value)
                OTbHocVien.Anh = (string)idr["Anh"];
            if (idr["MaLop"] != DBNull.Value)
                OTbHocVien.MaLop = (int)idr["MaLop"];
            if (idr["MaDonVi"] != DBNull.Value)
                OTbHocVien.MaDonVi = (int)idr["MaDonVi"];
            if (idr["NganhNgheDaoTao"] != DBNull.Value)
                OTbHocVien.NganhNgheDaoTao = (string)idr["NganhNgheDaoTao"];
            if (idr["ChucVu"] != DBNull.Value)
                OTbHocVien.ChucVu = (int)idr["ChucVu"];
            if (idr["PhongBan"] != DBNull.Value)
                OTbHocVien.PhongBan = (int)idr["PhongBan"];
            if (idr["TrinhDo"] != DBNull.Value)
                OTbHocVien.TrinhDo = (int)idr["TrinhDo"];
            if (idr["TDVanHoa"] != DBNull.Value)
                OTbHocVien.TDVanHoa = (int)idr["TDVanHoa"];
            if (idr["HocHam"] != DBNull.Value)
                OTbHocVien.HocHam = (string)idr["HocHam"];
            if (idr["TinhTrangHD"] != DBNull.Value)
                OTbHocVien.TinhTrangHD = (string)idr["TinhTrangHD"];
            if (idr["DiaChi"] != DBNull.Value)
                OTbHocVien.DiaChi = (string)idr["DiaChi"];
            if (idr["GhiChu"] != DBNull.Value)
                OTbHocVien.GhiChu = (string)idr["GhiChu"];
            return OTbHocVien;
        }
////-----------------------------------------------------------//
        //public static bool TestByID(string MaHV)
        //{
        //    SqlParameter[] pr = new SqlParameter[2];
        //    pr[0] = new SqlParameter(@"MaHV",MaHV);
        //    pr[1] = new SqlParameter(@"TestID",SqlDbType.Bit);
        //    pr[1].Direction = ParameterDirection.Output;
        //    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbHocVien_TestByID", pr);
        //    return Convert.ToBoolean(pr[1].Value);
        //}
//-----------------------------------------------------------//
        //public static List<ETbHocVien> ListAll()
        //{
        //    List<ETbHocVien> list = new List<ETbHocVien>();
        //    IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbHocVien_SelectAll", null);
        //    while (idr.Read())
        //      list.Add(GetOneTbHocVien(idr));
        //    if (idr.IsClosed == false)
        //    {
        //       idr.Close();
        //       idr.Dispose();
        //    }
        //    return list;
        //}

        //public static List<ETbHocVien> ListTop(int Top)
        //{
        //    SqlParameter[] pr = new SqlParameter[1];
        //    pr[0] = new SqlParameter(@"Top", Top);
        //    List<ETbHocVien> list = new List<ETbHocVien>();
        //    IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbHocVien_SelectAll", pr);
        //    while (idr.Read())
        //      list.Add(GetOneTbHocVien(idr));
        //    if (idr.IsClosed == false)
        //    {
        //       idr.Close();
        //       idr.Dispose();
        //    }
        //    return list;
        //}
    //    public static DataTable SelectStudentsBySearch(ETbHocVien OTbHocVien)
    //    {
    ////            @Id nchar(15), @Name nvarchar(50),
    ////@Age int, @Address nvarchar(500), 
    ////@HomeTown nvarchar(300),@WorkingTime int,
    ////@IdRegency int, @IdUnit int,
    ////@IdQualification int, @Number int, @idLanguage int
    //        SqlParameter[] pr = new SqlParameter[17];
    //        pr[0] = new SqlParameter(@"Id", OTbHocVien.MaHV);
    //        pr[1] = new SqlParameter(@"Name", OTbHocVien.TenHV);
    //        pr[2] = new SqlParameter(@"Age", OTbHocVien.Tuoi);
    //        pr[3] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan);
    //        pr[4] = new SqlParameter(@"Address", OTbHocVien.DiaChi);
    //        pr[5] = new SqlParameter(@"WorkingTime", OTbHocVien.ThoiGianCT);
    //        pr[6] = new SqlParameter(@"Sdt", OTbHocVien.Sdt);
    //        pr[7] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT);
    //        pr[9] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
    //        pr[10] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
    //        pr[11] = new SqlParameter(@"MaNganh", OTbHocVien.MaNganh);
    //        pr[12] = new SqlParameter(@"MaChucVu", OTbHocVien.MaChucVu);
    //        pr[13] = new SqlParameter(@"MaPB", OTbHocVien.MaPB);
    //        pr[14] = new SqlParameter(@"MaTDChuyenMon", OTbHocVien.MaTDChuyenMon);
    //        pr[15] = new SqlParameter(@"MaTDHocVan", OTbHocVien.MaTDHocVan);
    //        pr[14] = new SqlParameter(@"Number", OTbHocVien.MaTDChuyenMon);            

    //        DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectStudentsBySearch", null);
    //        return all;
    //    }
//-----------------------------------------------------------//
      
//-----------------------------------------------------------//
        public static void UpdateInfoHV(ETbHocVien HocVien)
        {
            SqlParameter[] pr = new SqlParameter[18];
            pr[0] = new SqlParameter(@"MaHV", HocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", HocVien.TenHV);
            pr[2] = new SqlParameter(@"Ngaysinh", HocVien.NgaySinh);
            pr[3] = new SqlParameter(@"QueQuan", HocVien.QueQuan);
            pr[4] = new SqlParameter(@"DiaChi", HocVien.DiaChi);
            pr[5] = new SqlParameter(@"Sdt", HocVien.Sdt);
            pr[6] = new SqlParameter(@"Anh", HocVien.Anh);
            pr[7] = new SqlParameter(@"GhiChu", HocVien.GhiChu);
            pr[8] = new SqlParameter(@"Ten", HocVien.Ten);
            pr[9] = new SqlParameter(@"NgayVaoNganh", HocVien.NgayVaoNganh);
            pr[10] = new SqlParameter(@"NgayVaoCoQuan", HocVien.NgayVaoCoQuan);
            pr[11] = new SqlParameter(@"NgayTuyenDung", HocVien.NgayTuyenDung);
            pr[12] = new SqlParameter(@"TinhTrangHD", HocVien.TinhTrangHD);
            pr[13] = new SqlParameter(@"NganhNgheDaoTao", HocVien.NganhNgheDaoTao);
            pr[14] = new SqlParameter(@"HinhThucDaoTao", HocVien.HinhThucDaoTao);
            pr[15] = new SqlParameter(@"TrinhDoQLKT", HocVien.TrinhDoQLKT);
            pr[16] = new SqlParameter(@"HocHam", HocVien.HocHam);
            pr[17] = new SqlParameter(@"HocVi", HocVien.HocVi);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "HocVienUpdate2", pr);
        }
    }
 
}